package com.wwwc.admin.web.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.SQLException;

import com.wwwc.admin.web.database.*;
import com.wwwc.util.web.*;

public class adminDatabaseManagerTable extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response)
	throws IOException, ServletException 
    {
	response.setContentType("text/html");

	String user_ip       = request.getRemoteAddr();
	HttpSession session  = request.getSession(true);
        PrintWriter out      = response.getWriter();

	ResourceBundle config = (ResourceBundle)session.getAttribute("Configuration");
	
	if (config == null) {
	    return;
	}

	String local_ips = config.getString("3wcenter.local_ips");
	if(user_ip==null || !user_ip.startsWith(local_ips)) {
	    return;
	}

	String action     = request.getParameter("action");
	String database   = request.getParameter("database");	
	String table_name = request.getParameter("table_name");

	if(action == null) {
	    out.println("2:action==null");
	    return;
	}

	if(action==null || database == null || table_name == null) {
	    System.out.println("adminDatabaseManagerTable:Error:101");
	    return;
	}

	String call_path = request.getContextPath()+request.getServletPath();  

	int sw = 1280;
	int sh = 1024;

	String db_url      = config.getString("3wcenter.database."+database+".db_name");
	String db_driver   = config.getString("3wcenter.database."+database+".driver_name");
	String db_user     = config.getString("3wcenter.database."+database+".user_name");
	String db_pass     = config.getString("3wcenter.database."+database+".pass_word");
	String max_rows    = config.getString("3wcenter.content.max_rows");
	String backup_home = config.getString("3wcenter.databases.backup.home");

	Database db = new Database ();
	db.setDataBaseName(db_url);
	db.setDriverName(db_driver);
	db.setUserName(db_user);
	db.setPassword(db_pass);
	
	String query      = null;
	Vector prim_keys_v   = null;
	String page = request.getParameter("pid");
	if(page==null) {
	    page = "1";
	}

	adminDatabaseManagerBean abean = new adminDatabaseManagerBean();

	out.println(abean.databaseUtils(call_path, database)+"<BR>");

	if(action.equals("view_edit_record")){
	    out.println(abean.viewEditRecord(request, call_path, table_name, db, database, page, max_rows));
	}

	if(action.equals("table_info")){
	    out.println(abean.getTableInfo(db, table_name, true));
	}

	if(action != null && action.equals("load_data_from_file")) {
	    out.println(abean.loadDataFromFile(request, backup_home));
	}

	if(action.equals("display_html_page")) {
	    query = "SELECT FIELD9, FIELD10, FIELD11, FIELD12, FIELD13, FIELD14, FIELD15 FROM "+table_name+" ORDER BY FIELD4 DESC, FIELD5;";
	    db.select(query);
	   
	    Vector vr = db.getTableRecordVector();
	    Vector v = null;
	    out.println("<H3>Database ["+database+"]</H3>");
	    out.println("<H3>Table ["+table_name+"]</H3>");
	    out.println("<FORM method=post action=adminDatabaseManagerTable>");
	    out.println("<TABLE border=1 style='font-size: 10pt' width=800>");

	    Enumeration enum = vr.elements();
	    Enumeration enumr = null;
	    while(enum.hasMoreElements()) {
		v = (Vector)enum.nextElement();
		enumr = v.elements();
		while(enumr.hasMoreElements()) {
		    out.println("<TR><TD>"+enumr.nextElement()+"</TD></TR>");
		}
	    }
	    enum = null;
	    enumr = null;
	    vr = null;
	    v = null;
	    out.println("</TABLE>");
	    out.println("<input type=hidden name=action value=3wc_user_manager>");
	    out.println("<input type=hidden name=database value="+database+">");
	    out.println("<input type=hidden name=table_name value="+table_name+">");
	    out.println("</FORM>");
	}
	
	if(action.equals("make_update_form")) {
	    Vector rh = new Vector();
	    Vector rt = new Vector();
	    Vector rv = new Vector();
	    Vector rs = new Vector();

	    query = request.getParameter("query");
	    db.select(query, table_name, database);

	    rh = db.getTableColumnNameVector();
	    rt = db.getTableColumnTypeVector();
	    rs = db.getTableColumnSizeVector();
	    Vector v = db.getTableRecordVector();

	    if(!v.isEmpty()) {
		rv = (Vector)v.elementAt(0);
	    }

	    prim_keys_v = db.getTablePrimaryKeysVector();

	    out.println("<HTML><HEAD><TITLE></TITLE></HEAD><BODY><CENTER>");
	    out.println("<H3>Table["+table_name+"] in Database ["+database+"]</H3>");

	    int cln = rt.size();
	    String temp  = null;
	    String temph = null;
	    String tempt = null;

	    out.println("<FORM method=post action=adminDatabaseManagerTable>");
	    out.println("<TABLE border=1 style='font-size: 8pt' width=100%>");
	    out.println("<TR><TD><B>NAME</B></TD><TD><B>TYPE</B></TD><TD><B>VALUE</B></TD></TR>");
	    
	    for(int n=0; n<cln; n++) {

		temph = (String)rh.elementAt(n);
		if(prim_keys_v.contains(temph)) {
		    out.println("<TR><TD>*"+temph+"</TD>");
		}
		else {
		    out.println("<TR><TD>&nbsp;"+temph+"</TD>");
		}

		tempt = ""+rt.elementAt(n);
		out.println("<TD>"+tempt+"</TD>");
		
		temp = ""+rv.elementAt(n);
		if(temp == null || temp.length()<1) {
		    temp = "";
		}
		else {
		    if(temp.indexOf("\"") !=-1) {
			temp = temp.replaceAll("\"","&quot;");
		    }
		}

		out.println("<TD><input type=text name="+temph+" value=\""+temp+"\" size=100></TD></TR>");
	    }
	    
	    out.println("</TABLE>");
	    out.println("<BR><input type=submit value=Update  style='font-size: 8pt'>");
	    out.println("<input type=hidden name=action value=make_update_query>");
	    out.println("<input type=hidden name=database value="+database+">");
	    out.println("<input type=hidden name=table_name value="+table_name+">");
	    out.println("<input type=hidden name=query value=\""+query+"\">");
	    out.println("<input type=hidden name=pid value="+page+">");
	    out.println("</FORM>");
	    out.println("</CENTER></BODY></HTML>");
	    return;
	}

	if(action.equals("make_update_query")) {
	    Vector rh = new Vector();
	    Vector rt = new Vector();
	    Vector rv = new Vector();
	    Vector rs = new Vector();

	    query = request.getParameter("query");
	    db.select(query, table_name, database);

	    rh = db.getTableColumnNameVector();
	    rv = (Vector)(db.getTableRecordVector()).elementAt(0);
	    rt = db.getTableColumnTypeVector();
	    rs = db.getTableColumnSizeVector();
	    prim_keys_v = db.getTablePrimaryKeysVector();

	    int cln = rt.size();
	    String temp  = null;
	    String tempv = null;
	    String temph = null;
	    String tempt = null;

	    StringBuffer query_bf = new StringBuffer();
	    StringBuffer where_clu_bf = new StringBuffer("");

	    query_bf.append("UPDATE "+table_name+" SET ");

	    for(int m=0; m<cln; m++) {
		temph = (String)rh.elementAt(m);
		tempv = ""+rv.elementAt(m);

		temp  = ""+request.getParameter(temph);
		tempt = (String)rt.elementAt(m);
		
		if(tempv == null || tempv.length()<1) {
		    tempv = "";
		}

		if(prim_keys_v.isEmpty() && m==0) {
		    if(tempt.equals("VARCHAR") || tempt.equals("CHAR") || tempt.equals("LONGCHAR")){
			where_clu_bf.append(temph+"='"+tempv+"';");
		    }
		    if(tempt.equals("INTEGER") || tempt.equals("DOUBLE") || 
		       tempt.equals("NUMERIC") || tempt.equals("BIGINT") ||
		       tempt.equals("SMALLINT")){
			where_clu_bf.append(temph+"="+tempv+" ;");
		    }
		}
		else {
		    if(prim_keys_v.contains(temph)) {
			if(tempt.equals("VARCHAR") || tempt.equals("CHAR") || tempt.equals("LONGCHAR")){
			    if(temph.equals((String)prim_keys_v.lastElement())) {
				where_clu_bf.append(temph+"='"+tempv+"';");
			    }
			    else {
				where_clu_bf.append(temph+"='"+tempv+"' AND ");
			    }
			}
			if(tempt.equals("INTEGER") || tempt.equals("DOUBLE") || 
			   tempt.equals("NUMERIC") || tempt.equals("BIGINT") ||
			   tempt.equals("SMALLINT")){
			    if(temph.equals((String)prim_keys_v.lastElement())) {
				where_clu_bf.append(temph+"="+tempv+";");
			    }
			    else {
				where_clu_bf.append(temph+"="+tempv+" AND ");
			    }
			}
		    }
		}

		if(!tempv.equals(temp)) {
		    if(temp.indexOf("'") !=-1) {
			temp = temp.replaceAll("'","''");
		    }

		    if(temp.indexOf("&quot;") !=-1) {
			temp = temp.replaceAll("&quot;","\"");
		    }

		    if(tempt.startsWith("VARCHAR") || tempt.equals("CHAR") || tempt.equals("LONGCHAR") || tempt.equals("TIMESTAMP")){
			query_bf.append(temph+"='"+temp+"'");
		    }
		    else if(tempt.equals("INTEGER") || tempt.equals("DOUBLE") || 
		       tempt.equals("NUMERIC") || tempt.equals("BIGINT") ||
		       tempt.equals("SMALLINT") || tempt.equals("BIT")){
			query_bf.append(temph+"="+temp);
		    }
		    else {
			out.println("Data type not found["+tempt+"]");
			return;
		    }
		    query_bf.append(",");
		}
	    }

	    temp = query_bf.toString();
	    
	    if(temp.endsWith("SET") || temp.endsWith("SET ")) {
		out.println(query);
		RequestDispatcher rd = request.getRequestDispatcher("adminDatabaseManagerTable?action=view_edit_record&pid="+page); 
		rd.forward(request,response);
		return;
	    }

	    query = temp.substring(0,temp.length()-1)+" WHERE "+where_clu_bf.toString();
	    
	    request.setAttribute("query", query);
	    
	    RequestDispatcher rd = request.getRequestDispatcher("adminDatabaseManagerQuery?action=update_record&pid="+page); 
	    rd.forward(request,response);
	    return;
	}

	if(action.equals("3wc_user_manager")) {
	    query = "SELECT * FROM USER_INFO ORDER BY USERNAME";
	    db.select(query);
	    Vector vu = db.getTableRecordVector();

	    out.println("<HTML><HEAD><TITLE></TITLE></HEAD><BODY><CENTER>");
	    out.println("<H3>All User List</H3>");
	    out.println("<FORM method=post action=adminDatabaseManagerTable>");
	    out.println("<TABLE border=1 style='font-size: 8pt'>");

	    Enumeration enum = vu.elements();
	    while(enum.hasMoreElements()) {
		out.println("<TR><TD>"+enum.nextElement()+"</TD></TR>");
	    }
	    
	    out.println("</TABLE>");
	    out.println("<BR><input type=submit value=Update  style='font-size: 8pt'>");
	    out.println("<input type=hidden name=action value=3wc_user_manager>");
	    out.println("<input type=hidden name=database value="+database+">");
	    out.println("<input type=hidden name=table_name value="+table_name+">");
	    out.println("</FORM>");
	    out.println("</CENTER></BODY></HTML>");
	    return;
	}
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
	throws IOException, ServletException 
    {
	doPost(request, response);
    }
}








